with tmp_electronic_package_list as (
    select 
        id                   
        ,package_code         
        ,package_name         
        ,package_property     
        ,package_property_name
        ,package_type         
        ,package_type_name    
        ,center_id            
        ,center_code          
        ,center_name          
        ,package_number       
        ,print_number         
        ,print_times_number   
        ,create_by            
        ,create_by_code       
        ,create_by_name       
        ,create_network_id    
        ,create_network_code  
        ,create_network_name  
        ,create_time          
        ,is_used              
        ,used_time            
        ,row_number() over(partition by package_number order by dt desc) as row_id
    from jms_ods.electronic_package_list
    where dt >= date_add('{{ execution_date | cst_ds }}', -90)
    and dt <= '{{ execution_date | cst_ds }}'
)

insert overwrite table jms_dwd.dwd_tab_electronic_package_list_base_dt partition(dt)
select 
    id                    
    ,package_code         
    ,package_name         
    ,package_property     
    ,package_property_name
    ,package_type         
    ,package_type_name    
    ,center_id            
    ,center_code          
    ,center_name          
    ,package_number       
    ,print_number         
    ,print_times_number   
    ,create_by            
    ,create_by_code       
    ,create_by_name       
    ,create_network_id    
    ,create_network_code  
    ,create_network_name  
    ,create_time          
    ,is_used              
    ,used_time            
    ,substr(create_time,1,10) as dt 
from tmp_electronic_package_list 
where row_id = 1 
and substr(create_time,1,10) >= date_add('{{ execution_date | cst_ds }}', -90)
distribute by dt
;
